package com.sis;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.Scanner;
import java.sql.*;

import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.w3c.dom.*;

import javax.xml.parsers.*;

import java.io.*;
import java.nio.file.Path;

import org.apache.http.HttpEntity;
import org.apache.http.auth.AuthScope;
import org.apache.http.auth.UsernamePasswordCredentials;
import org.apache.http.client.CredentialsProvider;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.conn.ssl.AllowAllHostnameVerifier;
import org.apache.http.entity.FileEntity;
import org.apache.http.entity.mime.MultipartEntityBuilder;
import org.apache.http.entity.mime.content.ContentBody;
import org.apache.http.entity.mime.content.FileBody;
import org.apache.http.entity.mime.content.StringBody;
import org.apache.http.impl.client.BasicCredentialsProvider;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.protocol.HTTP;
import org.apache.http.util.EntityUtils;

import java.util.*;  
import javax.mail.*;  
import javax.mail.internet.*;  
import javax.activation.*;  



public class AutoSIS {

	public static void main(String[] args) throws Exception {
		
		//Main Stuff
		
		

	String options = args[0];
			switch (options){
			
			case "install":
				createMainProperties();
				CreateFoldersAndIntegrationPropertiesFile();
				break;
			case "run":
				SISAutoRun();
				break;
			
			}
		help();
		
		
		

	}
	
	
	
	/*Help output with docs
	 * 
	 */
	
	public static void help(){
		
		System.out.println("All Options, use:  java -jar install/run/help");
		
	}
	
	
	/*SIS Run.
	 * process the files in the folders.
	 * 
	 * 
	 */
	
	
	
	public static void SISAutoRun() throws Exception{
		
		File directory = new File(System.getProperty("user.dir"));
		File[] fList = directory.listFiles();
		
		for (File file : fList){
	        if (file.isDirectory()){
	            System.out.println("Using Folder: "+file.getName());
	            File fileFind = new File(file.getName() + "/properties.txt"); 
	            boolean exists = fileFind.exists();  
	            
		            if (exists) {  
	                    System.out.println("Valid folder with properties file. Looking for Feed files in: "+System.getProperty("user.dir")+"/"+file.getName()); 
	                                                           
	                    	
	                    //File SubDirectory = new File(System.getProperty("user.dir")+"\\"+file.getName()+"\\");  
	                    File SubDirectory = new File(System.getProperty("user.dir")+"/"+file.getName()+"/");  
	                    File[] fullList = SubDirectory.listFiles();
	                    
	                    	for (File files : fullList){
	                    		
	                    		if (!files.getName().equals("properties.txt"))  { System.out.println("Valid feed file, using: "+files.getName() );
	                    		
	                    		//Do the SIS upload part.
	                    		
	                    	
	                    		String postfeedback = Postrequest(file.getName(),files.getName());
	                    		
	                    		//Parse the Feedback to get the 
	                    		
	                      		String intRef = getreffromString(postfeedback);
	                    		//Count the records of the file		
	                    		
	                      		LineNumberReader  lnr = new LineNumberReader(new FileReader(new File(System.getProperty("user.dir")+"/"+file.getName()+"/"+files.getName())));
	                      		lnr.skip(Long.MAX_VALUE);
	                    		System.out.println("Total records in feed File: "+(lnr.getLineNumber()-1));

	                    		//Set the total row count for the feed file minus the header.
	                    		int Readcount = (lnr.getLineNumber()-1);

	                    		// Finally, the LineNumberReader object should be closed to prevent resource leak
	                    		lnr.close();

	                    		                    		
	                    		//5 second wait.
		                    	Thread.sleep(5000);
		                    		
			                    		
		                    		//Pass the full Status of the intergation XML to a parser to get the Total Count.
		                    		String DataXML = getRequestIntegrationStatus(file.getName(),intRef);
		                    	
		                    		//Convert total count to an integer. 
		                    		int totalcount = (Integer.parseInt(totalcountXML(DataXML,1)));
		                    		int escnorecords = 0;
		                    		
		                    		//While the totalcount of records processed is less than the row count of the file we keep checking every  30 seconds.
		                    		while (totalcount < (Readcount)){
		                    			 
		                    			 
		                    			 DataXML = getRequestIntegrationStatus(file.getName(),intRef);
		                    			 totalcount = Integer.parseInt(totalcountXML(DataXML,1));
		                    			 Thread.sleep(30000);
		                    			 
		                    			 //if the feed file in invalid then no rows will be processed.  This will break of checking the feed file after 3 Mins. If no rows have been processed in that time then it will failed so it can move 
		                    			 	if (totalcount == 0) escnorecords++;
		                    			 	if (escnorecords >5)System.out.print("This intergation is not procssing any records.  It may be a bad feed file or the wrong end point");
		                    			 	if (escnorecords >5)  break;
		                    			 		
		                    			 
		                    				}
	                    		System.out.println("The file "+files.getName()+" has been processed");
	                    		String[] IntergrationProperties = getintegrationPropertiesValues(file.getName());		                   		
	                    		String[] databaseproperties = getDatabaseandHostName();
	                    		
	                    		//Check if Send Email is selected in Prop file.  If so send email with the SIS summary.      		
	                    		if (databaseproperties[6].equals("Y")) sendmail("Start Date: "+totalcountXML(DataXML, 5)+"\r\n"+" Warning Count: "+ totalcountXML(DataXML, 2)+"\r\n"+" Error Count: "+totalcountXML(DataXML, 3)+"\r\n"+" Completed count: "+totalcountXML(DataXML, 1)+"\r\n"+" End Date: "+totalcountXML(DataXML, 4));
	                    		
	                    		
	                    		
	                    		      		//If the Intergration is set to delete the file after it is processed delete it
	                    		      		if (IntergrationProperties[7].equalsIgnoreCase("Y")){
	                    		      		//Delete the file
	                    		      		//String execPath = System.getProperty("user.dir")+"\\"+file.getName()+"\\"+files.getName();
			                    			String execPath = System.getProperty("user.dir")+"/"+file.getName()+"/"+files.getName();
			                    			File fdelete = new File(execPath);
				                    		fdelete.delete();
				                    		System.out.println("Feed file "+files.getName()+" has been deleted");
		                    		}
			                    			
	                    		
		                    		}
		                    		else{
		                    		
		                    		System.out.println("This are no feed files to process in this folder "+files.getName());
		                    		}
                 		
	                    	}
	                    	
	                    
		            }else   {
		                	
		                	System.out.println("No properties file found in this folder");
		                	
		                }
	        	}			
	        }
		
}
	
	
	
	


	
	
	
	
	/*Get the Current processed rows of a running Integration via the Web-Service 
	 * Pass in the Log reference id
	 */
	
	public static String getRequestIntegrationStatus(String Intname, String intRef) throws Exception {
	      
       	String[] databaseproperties = getDatabaseandHostName();
	    String[] IntergrationProperties = getintegrationPropertiesValues(Intname);
	    
	      
    	
    	CredentialsProvider credsProvider = new BasicCredentialsProvider();
        credsProvider.setCredentials(
                new AuthScope(databaseproperties[5], 443),
                new UsernamePasswordCredentials(IntergrationProperties[3], IntergrationProperties[2]));
        CloseableHttpClient httpclient = HttpClients.custom()
        		.setHostnameVerifier(new AllowAllHostnameVerifier())
                .setDefaultCredentialsProvider(credsProvider)
                .build();
			        try {
			        	
			            HttpGet httpget = new HttpGet("https://"+databaseproperties[5]+"/webapps/bb-data-integration-flatfile-"+databaseproperties[0]+"/endpoint/dataSetStatus/"+intRef);
			            CloseableHttpResponse response = httpclient.execute(httpget);
			            
			            
			            try {
			               
			                //Used for debug.
			               // System.out.println("URL Responce Status from getting Log counts: "+response.getStatusLine());
			               
			                String responseString = EntityUtils.toString(response.getEntity());            
			                return responseString;
			                	                
			            } finally {
			                response.close();
			            }
			        } finally {
			            httpclient.close();
			        }
					
    }


	
	/*Upload Feed File to SIS Web-Service
	 * This is a post request and uses both the main properties file and the Integration properties
	 * 
	 * 
	 */
	
	public static String Postrequest(String Intname, String SISfilename) throws Exception {
		
		String[] databaseproperties = getDatabaseandHostName();
		String[] IntergrationProperties = getintegrationPropertiesValues(Intname);
		
		CredentialsProvider credsProvider = new BasicCredentialsProvider();
        credsProvider.setCredentials(
                new AuthScope(databaseproperties[5], 443),
                new UsernamePasswordCredentials(IntergrationProperties[3], IntergrationProperties[2]));
       
        
        
        CloseableHttpClient httpclient = HttpClients.custom()
        		//.setHostnameVerifier(new AllowAllHostnameVerifier())
                .setDefaultCredentialsProvider(credsProvider)
                .build();
        
        
	        try {
	        	

	          
	            HttpPost httppost = new HttpPost("https://"+databaseproperties[5]+"/webapps/bb-data-integration-flatfile-"+databaseproperties[0]+"/endpoint/"+IntergrationProperties[5]+"/"+IntergrationProperties[4]);
	            //Set the SIS file to upload.
	          
	            File staticFile = new File(IntergrationProperties[6]+"/"+SISfilename);
	            if (staticFile.exists()){ System.out.println("Using  file with intrergration or Folder name "+IntergrationProperties[6]);}
	            
	            HttpEntity entity = new FileEntity(staticFile, "text/plain");
	          
	            //Add file to httppost
	            httppost.setEntity(entity);
	            //Call http post
	            System.out.println("Executing request " + httppost.getRequestLine());
	            CloseableHttpResponse response = httpclient.execute(httppost);
	            
		            try {
		                		                
		                System.out.println(response.getStatusLine());
		                //Get output  of responce  and cast to a string
		                String responseString = EntityUtils.toString(response.getEntity());
		                System.out.println(responseString);
		                EntityUtils.consume(response.getEntity());
		
		                return responseString;
  
		            } finally {
		                response.close();
		            }
		        } finally {
		            httpclient.close();
		        }
	
	}
	

	
	
	
	
	/*Create the Main  Properties file 
	 * Use user input to create the setup file for Database and host connections.
	 * 
	 */
	
	
	public static void createMainProperties() throws IOException{
		
		File f = new File("properties.txt");
		if(!f.exists()){
			System.out.println("Main Config File Not Found, Creating New Config in : "+System.getProperty("user.dir"));	
			f.createNewFile();
			Scanner user_input = new Scanner( System.in );
			System.out.print("Schema name. usually BBLEARN or BB_BB60: ");
				String Schema = user_input.next();
			System.out.print("Database HostName: ");
				String Database_host = user_input.next();
			System.out.print("Database port.  Normally 1433: ");
				String Database_port = user_input.next();
			System.out.print("Database User: ");
				String UserName = user_input.next();
			System.out.print("Database password: ");
				String passwd = user_input.next();
			System.out.print("Webserver Hostname: ");
				String WebserviceURL = user_input.next();
			System.out.print("Send Email Reports Y/N: ");
				String SendEmail = user_input.next();
			System.out.print("SMTP Server: ");
				String SMTPserver = user_input.next();
			System.out.print("Mail From Address: ");
				String EmailFrom = user_input.next();
			System.out.print("Mail too Address: ");
				String EmailTo = user_input.next();
				
				FileWriter pw = new FileWriter("properties.txt");
				BufferedWriter bw = new BufferedWriter(pw);
				bw.write("Schema="+Schema+"\r\n"+"Database_host="+Database_host+"\r\n"+"Database_port="+Database_port+"\r\n"+"UserName="+UserName+"\r\n"+"passwd="+passwd+"\r\n"+"WebserviceURL="+WebserviceURL+"\r\n"+"SendEmail="+SendEmail+"\r\n"+"SMTPserver="+SMTPserver+"\r\n"+"EmailFrom="+EmailFrom+"\r\n"+"EmailTo="+EmailTo+"\r\n");
				bw.close();
				user_input.close();
		}
		
		
		System.out.println("Properties File Now Exists in the Dir "+ System.getProperty("user.dir"));
		
	}
	
	
	
	
	/*This Method will create the  Integration properties files in each folder. 
	 *  This file will be used to get the Integration settings when makcing calls to the webservice
	 * 
	 */
	
	
	public static void CreateFoldersAndIntegrationPropertiesFile() throws IOException {

		  String[] fullconfig = getDatabaseandHostName();  	   
	      String connectionUrl = "jdbc:sqlserver://"+fullconfig[1]+":"+fullconfig[2]+";" +
	         "databaseName="+fullconfig[0] + ";user="+fullconfig[3] +";password="+fullconfig[4];

	      // Declare the JDBC objects.
	      Connection con = null;
	      Statement stmt = null;
	      ResultSet rs = null;

		      try {
		         // Establish the connection.
		         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		         con = DriverManager.getConnection(connectionUrl);	
		         String SQL = "SELECT  name,extended_data, description, type_handle, data_source_batch_uid, auth_password, guid FROM DATA_INTGR";
		         stmt = con.createStatement();	                  
		         rs = stmt.executeQuery(SQL);
	
		         // Iterate through the data in the result set add to strings.
		         while (rs.next()) {
		            
		        
		            String Nameofintergration = rs.getString("name");
		            String description = rs.getString("description"); 		           
		            String data_source_batch_uid = rs.getString("data_source_batch_uid");
		            String auth_password = rs.getString("auth_password");
		            String auth_user = rs.getString("guid");
		            String StoreRefreshDeleteRefreshlegacy = "Store";
		            String end_point = "course";
		            	                      
															
		            //Create the folders
		            
			            File fld = new File(Nameofintergration);
			            if (fld.exists()) {
			            	System.out.println("Folder allready exists:  ");
			               
			            }
				            else
				            {
				            	
				            new File(Nameofintergration).mkdir();	
				            System.out.println("Created file name "+Nameofintergration);	
				            	
				            }
		            
		            //Add a Properties file to each folder
			            FileWriter pw = new FileWriter(Nameofintergration+"/properties.txt"); 
			            BufferedWriter bw = new BufferedWriter(pw);									
						bw.write("Nameofintergration="+Nameofintergration+"\r\n"+"description="+description+"\r\n"+"data_source_batch_uid="+data_source_batch_uid+"\r\n"+"auth_password="+auth_password+"\r\n"+"auth_user="+auth_user+"\r\n"+"StoreRefreshDeleteRefreshlegacy="+StoreRefreshDeleteRefreshlegacy+"\r\n"+"end_point="+end_point+"\r\n"+"Delete_File_After_Processed=N"+"\r\n");
						bw.close();
						
		         }
		      }

	      // Handle any errors that may have occurred.
	      catch (Exception e) {
	         e.printStackTrace();
	      }
	      finally {
	         if (rs != null) try { rs.close(); } catch(Exception e) {}
	         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	         if (con != null) try { con.close(); } catch(Exception e) {}
	      }
	
	   }
	
	
	
	
	
	
	/* Read the Integration properties files.  Pass in the folder name to use the method.
	 * This will return the String array integration with the name of the integration, Description, Authentication password,
	 * Authentication User-name, data Update type store,Refresh etc , End_point course,person etc and the Folder name (Usually  this will be the same as the Integration name is auto setup.
	 * Manual  folder can be added though so multiple  folders  can  use the same Integration
	 */
	
	public static String[] getintegrationPropertiesValues(String pathtoSISFolder) throws IOException {
	

		Properties prop = new Properties();
		String propFileName = pathtoSISFolder+"/"+"properties.txt";
		 
		InputStream inputStream = new FileInputStream(propFileName);

		prop.load(inputStream);
			
				String[] integrationConfig = {prop.getProperty("Nameofintergration"),prop.getProperty("description"),prop.getProperty("auth_password"),prop.getProperty("auth_user"),prop.getProperty("StoreRefreshDeleteRefreshlegacy"),prop.getProperty("end_point"),pathtoSISFolder,prop.getProperty("Delete_File_After_Processed")};
				
				
				return integrationConfig;
	}
	
	
	
	
	/* Read the Main Properties file 
	 * This will return the details created on install by the installer or by hand
	 * The config will return the Schema name, the DB hostName, the DB port, The User-name, password and the Main Web-service URL
	 */
	
	public static String[] getDatabaseandHostName() throws IOException {
		 
		Properties prop = new Properties();
		String propFileName = "properties.txt";
		 
		InputStream inputStream = new FileInputStream(propFileName);

		prop.load(inputStream);
				
				String[] Config = {prop.getProperty("Schema"),prop.getProperty("Database_host"),prop.getProperty("Database_port"),prop.getProperty("UserName"),prop.getProperty("passwd"),prop.getProperty("WebserviceURL"),prop.getProperty("SendEmail"),prop.getProperty("SMTPserver"),prop.getProperty("EmailFrom"),prop.getProperty("EmailTo")};
									
				return Config;
		}


	/*XML parser of the data status XML
	 * 
	 * 
	 */
	
	public static String totalcountXML(String dataSetStatus, int dataelement) throws ParserConfigurationException, SAXException, IOException {

		
		  DocumentBuilderFactory dbf =
		  DocumentBuilderFactory.newInstance();
		  DocumentBuilder db = dbf.newDocumentBuilder();
		  InputSource is = new InputSource();
		  is.setCharacterStream(new StringReader(dataSetStatus));
		
		  Document doc = db.parse(is);
		  NodeList nodes = doc.getElementsByTagName("dataSetStatus");
		
		  
			
				    Element element = (Element) nodes.item(0);
				     
				    // Pass back the element requested.  
				     switch (dataelement){
				     case 1: 	//Get the Completed count.
					     NodeList completedCount = element.getElementsByTagName("completedCount");
					     Element line1 = (Element) completedCount.item(0);
					     String ccount = getCharacterDataFromElement(line1);
					     System.out.println("Total records processed: " + getCharacterDataFromElement(line1));
				     	return ccount;
				     case 2:	//Get the WarningCount
					     NodeList warningCount = element.getElementsByTagName("warningCount");
					     Element line2 = (Element) warningCount.item(0);
					     String wcount = getCharacterDataFromElement(line2);
					     System.out.println("Warning count: "+wcount);
			     		return 	wcount;
				     case 3:	//Get  the Error count
					     NodeList errorCount = element.getElementsByTagName("warningCount");
					     Element  line3 = (Element) errorCount.item(0);
					     String ecount = getCharacterDataFromElement(line3);
					     System.out.println("Error count: "+ecount);
				     	return ecount;
				     case 4:	//Get the LastDate
					     NodeList lastEntryDate = element.getElementsByTagName("lastEntryDate");
					     Element  line4 = (Element) lastEntryDate.item(0);
					     String lastdate = getCharacterDataFromElement(line4);
					     System.out.println("Last Date: "+lastdate);
				     	return lastdate;
				     case 5: 	//Get the StartDate
					     NodeList startDate = element.getElementsByTagName("startDate");
					     Element  line5 = (Element) startDate.item(0);
					     String sDate = getCharacterDataFromElement(line5);
					     System.out.println("StartDate : "+sDate);
				     	return sDate;
				     				     
				     
				    }
					return null;
				     
				     
			     	    
	
	}

	public static String getCharacterDataFromElement(Element e) {
		Node child = e.getFirstChild();
		if (child instanceof CharacterData) {
		 CharacterData cd = (CharacterData) child;
		 return cd.getData();
		}
		return "?";
	}


/*Get the ref. ID from the URL repsonce. 
 * 
 * 
 * 
 * 
 */
	public static String getreffromString(String fullrepsoncestring) throws IOException{
	
	
		StringReader reader = new StringReader(fullrepsoncestring);  
		BufferedReader br = new BufferedReader(reader);
		String line;
		while ((line = br.readLine()) != null) {
		  String[] words = line.split(" ");
		  for (String word : words) {
		      if (word.length() >= 32) {
		      	
		      	
		          return word;
		      }
		  }
		}
			return null;
		
		}
		
	
	
///Send Email Client
	
	
	public static void sendmail(String messagebody) throws IOException {
		
		
		String[] databaseproperties = getDatabaseandHostName();
		
		
		String to = databaseproperties[9]; 
	    String from = databaseproperties[8]; 
	    String host = databaseproperties[7];  
	    Properties properties = System.getProperties();  
	      properties.setProperty("mail.smtp.host", host);  
	      Session session = Session.getDefaultInstance(properties);  
	      
	      try{  
	          MimeMessage message = new MimeMessage(session);  
	          message.setFrom(new InternetAddress(from));  
	          message.addRecipient(Message.RecipientType.TO,new InternetAddress(to));  
	          message.setSubject("SIS Updates");  
	          message.setText(messagebody);  
	   
	          // Send message  
	          Transport.send(message);  
	          System.out.println("message sent successfully....");  
	   
	       }catch (MessagingException mex) {mex.printStackTrace();}  
	      
		
	}
	
	
	
}
